# -*- encoding: utf-8 -*-
'''
@File    :   demo_mysql_test2.py
@Time    :   2022/11/26 23:11:35
@Author  :   Cms 
@Version :   1.0
@Contact :   2267000140@qq.com
@WebSite    :   www.baidu.com
'''

# here put the import lib
import mysql.connector
 
mydb = mysql.connector.connect(
  host="localhost",       # 数据库主机地址
  user="root",    # 数据库用户名
  passwd="2267000140aa!",   # 数据库密码
  database='runoob_db'
)

mycursor = mydb.cursor()


# 创建一个sites的数据表
# mycursor.execute("CREATE TABLE sites (name VARCHAR(255), url VARCHAR(255))")

# mycursor.execute("ALTER TABLE sites ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY")


# 遍历所有的数据表
# mycursor.execute("SHOW TABLES")

# for x in mycursor:
#   print(x)


# 插入数据

# sql = "INSERT INTO sites (name,url) VALUES (%s,%s)"
# val = ("百度","https://www.baidu.com")
# mycursor.execute(sql,val)

# mydb.commit() # 数据表内容有更新，必须使用到该语句

# print(mycursor.rowcount,"记录插入成功。")


# # 批量插入数据
# sql = "INSERT INTO sites (name, url) VALUES (%s, %s)"

# # 每一行数据用了一个元组
# val = [
#   ('Google', 'https://www.google.com'),
#   ('Github', 'https://www.github.com'),
#   ('Taobao', 'https://www.taobao.com'),
#   ('stackoverflow', 'https://www.stackoverflow.com/')
# ]

# mycursor.executemany(sql, val)
 
# mydb.commit()    # 数据表内容有更新，必须使用到该语句
 
# print(mycursor.rowcount, "记录插入成功。")


# 数据在插入后想获取
# sql = "INSERT INTO sites (name, url) VALUES (%s, %s)"
# val = ("Zhihu", "https://www.zhihu.com")
# mycursor.execute(sql, val)
 
# mydb.commit()
 
# print("1 条记录已插入, ID:", mycursor.lastrowid)


# 查询数据
# mycursor.execute("SELECT * FROM sites")
 
# myresult = mycursor.fetchall()     # fetchall() 获取所有记录
 
# for x in myresult:
#   print(x)


# 查询指定字段的内容
# mycursor.execute("SELECT name, url FROM sites")
 
# myresult = mycursor.fetchall()
 
# for x in myresult:
#   print(x)


# 只读取一条数据
# mycursor.execute("SELECT * FROM sites")
 
# myresult = mycursor.fetchone()
 
# print(myresult)


# 读取指定条件的数据，可以使用 where 语句
# sql = "SELECT * FROM sites WHERE name ='Github'"
 
# mycursor.execute(sql)
 
# myresult = mycursor.fetchall()
 
# for x in myresult:
#   print(x)


# 使用通配符 %
# sql = "SELECT * FROM sites WHERE url LIKE '%oo%'"
 
# mycursor.execute(sql)
 
# myresult = mycursor.fetchall()
 
# for x in myresult:
#   print(x)


# 为了防止数据库查询发生 SQL 注入的攻击，我们可以使用 %s 占位符来转义查询的条件
# sql = "SELECT * FROM sites WHERE name = %s"
# na = ("Taobao", )
 
# mycursor.execute(sql, na)
 
# myresult = mycursor.fetchall()
 
# for x in myresult:
#   print(x)


# 按 name 字段字母的升序排序
# sql = "SELECT * FROM sites ORDER BY name"
 
# mycursor.execute(sql)
 
# myresult = mycursor.fetchall()
 
# for x in myresult:
#   print(x)


# 按name字段字母的降序排序
# sql = "SELECT * FROM sites ORDER BY name DESC"
 
# mycursor.execute(sql)
 
# myresult = mycursor.fetchall()
 
# for x in myresult:
#   print(x)


# 设置查询的数据量 Limit

# mycursor.execute("SELECT * FROM sites LIMIT 3")
 
# myresult = mycursor.fetchall()
 
# for x in myresult:
#   print(x)


# 指定起始位置 OFFSET
# mycursor.execute("SELECT * FROM sites LIMIT 3 OFFSET 1")  # 0 为 第一条，1 为第二条，以此类推
 
# myresult = mycursor.fetchall()
 
# for x in myresult:
#   print(x)


# 删除记录使用 "DELETE FROM" 语句
# sql = "DELETE FROM sites WHERE name = 'stackoverflow'"
 
# mycursor.execute(sql)
 
# mydb.commit()
 
# print(mycursor.rowcount, " 条记录删除")


# 为了防止数据库查询发生 SQL 注入的攻击，我们可以使用 %s 占位符来转义删除语句的条件
# sql = "DELETE FROM sites WHERE name = %s"
# na = ("stackoverflow", )
 
# mycursor.execute(sql, na)
 
# mydb.commit()
 
# print(mycursor.rowcount, " 条记录删除")


# 更新表数据 UPDATE
# sql = "UPDATE sites SET name = 'ZH' WHERE name = 'Zhihu'"
 
# mycursor.execute(sql)
 
# mydb.commit()
 
# print(mycursor.rowcount, " 条记录被修改")


# 为防止数据库查询发生 SQL 注入的攻击，我们可以使用 %s 占位符来转义更新语句的条件：
# sql = "UPDATE sites SET name = %s WHERE name = %s"
# val = ("Zhihu", "ZH")
# mycursor.execute(sql, val)
 
# mydb.commit()
 
# print(mycursor.rowcount, " 条记录被修改")


# 删除表
# sql = "DROP TABLE IF EXISTS sites"  # 删除数据表 sites
 
# mycursor.execute(sql)